SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE FUNCTION [testing].[tvf_PublicCompaniesListByNAVDate] (@NAVDate DATE)

--DECLARE @NAVDate DATETIME = '10/24/2013'

RETURNS @RTN TABLE 
(
                  IssuerName varchar(100),
                  Ticker varchar(10),
                  IsQualified bit,
                  CurrNavDate smalldatetime,
                  PrevNAVDate SMALLDATETIME,
                  IssuerId INT,
                             FS_FundName varchar(MAX)
)
AS
-- body of the function
BEGIN
DECLARE @tbl TABLE 
(
                  IssuerName varchar(100),
                  Ticker varchar(10),
                  IsQualified bit,
                  CurrNavDate smalldatetime,
                  PrevNAVDate SMALLDATETIME,
                  IssuerId INT,
                             FS_FundName varchar(MAX)
)
  INSERT INTO @tbl
         
  SELECT     
              DISTINCT iss.IssuerName, UPPER( iss.Ticker) 'Ticker',  fi.IsQualified, tbl.CurrNavDate, tbl.PrevNAVDate, iss.IssuerId, mp.FS_FundName


       FROM FSFundModel.Reporting.FundIssuer fi
          INNER JOIN FSFundModel.Reporting.Issuer iss
          ON fi.ExportId  = iss.ExportId
          AND fi.IssuerId = iss.IssuerId
      
       
          CROSS APPLY (
                        SELECT   MAX(e.ExportId) ExportId ,
                                         f.CurrNavDate ,
                                         f.PrevNAVDate ,
                                         f.FundId
                        FROM     FSFundModel.Reporting.Export e
                                         INNER JOIN FSFundModel.Reporting.Fund f ON e.ExportId = f.ExportId
                                                                                                                 AND e.FundId = f.FundId
                        WHERE    e.ExportId IN ( SELECT  MAX(e.ExportId)
                                                                     FROM    FSFundModel.Reporting.Export e
                                                                     WHERE   e.NAVDate = @NAVDate
                                                                     GROUP BY e.FundId )
                        GROUP BY --e.FundId ,
                                         f.PrevNAVDate ,
                                         f.FundId ,
                                         f.CurrNavDate
                                         ) tbl
              CROSS APPLY (SELECT  * FROM ReportingScripts.dbo.mapSSB_PortID map 
              where ART_FundID = fi.fundid) mp



       WHERE fi.ExportId IN( SELECT    MAX(e.ExportId)  FROM FSFundModel.Reporting.Export e WHERE e.NAVDate = tbl.CurrNavDate   GROUP BY e.FundId )
       
       
       
       AND iss.IsPublicComp = 1
       AND fi.MktVal        > 0


          ORDER BY
       
              iss.IssuerName ASC   




DECLARE @FundC VARCHAR(MAX )
insert into @RTN
SELECT   DISTINCT  t.IssuerName ,
        t.Ticker ,
        t.IsQualified ,
        t.CurrNavDate ,
        t.PrevNAVDate ,
        t.IssuerId ,
        --t.FS_FundName,
              REPLACE(REPLACE( STUFF(( SELECT '|' + ti.FS_FundName FROM @tbl ti WHERE ti.IssuerName = t.IssuerName
              FOR XML PATH ('')), 1,1,''), ' ', ''), '|', ', ') FundNames

              FROM @tbl t



        RETURN ;

END


GO
